/*
 * Creates connection with the clients
 * Licensed by the Bunie Developmentgroup.
 */
package infoserver;

import Protocol.AccountManagementRequestPacket;
import Protocol.PacketTypeKeeper;
import com.microsoft.sqlserver.jdbc.SQLServerException;
import java.lang.Object;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author Patrick Wobben & Wouter Brunekreeft
 * @since 23-4-2012
 * @version 0.9
 *
 * The responsibily of this class is to create and maintain the connection and
 * enable the execution of queries
 */
public class DatabaseConnector {

    private Statement statement;
    private Connection connectionToDatabase;
    public final static String nullString = "null";
    private final static int SQLSERVERPORTNUMBER = 1433;
    private final static String defaultConnectionUrl =
            "jdbc:sqlserver://localhost:"
            + SQLSERVERPORTNUMBER
            + ";databaseName=Demo Database NAV (5-0); "
            + "integratedSecurity=true";
    private boolean result = false;

    public static void main(String[] args) {
        DatabaseConnector dc = new DatabaseConnector();

        AccountManagementRequestPacket amrp0 = new AccountManagementRequestPacket(1);
        amrp0.setTypeOfManagementPacket(PacketTypeKeeper.ADDACCOUNT);
        HashMap tempMap = new HashMap<String, String>();
        tempMap.put("windowsLogingID", "testWinId");
        amrp0.setAccountInformation(tempMap);


        AccountManagementRequestPacket amrp = new AccountManagementRequestPacket(1);
        amrp.setTypeOfManagementPacket(PacketTypeKeeper.ADDACCOUNT);
        amrp.setAccountInformation("testWinId", "testName", "testPass", "1");

        AccountManagementRequestPacket amrp1 = new AccountManagementRequestPacket(1);
        amrp1.setTypeOfManagementPacket(PacketTypeKeeper.ALTERACCOUNT);
        amrp1.setAccountInformation("testWinId", "testName", "testPass", "1");
        amrp1.setAlterAccountToo("myTestWinId", "myTestUser", "myTestPass", "0");

        AccountManagementRequestPacket amrp3 = new AccountManagementRequestPacket(1);
        amrp3.setTypeOfManagementPacket(PacketTypeKeeper.DELETEACCOUNT);
        amrp3.setAccountInformation("testWinId", "testName", "testPass", "1");

        dc.executeDeleteQuery(amrp0.getAccountInformation());
        dc.executeDeleteQuery(amrp3.getAccountInformation());
        dc.executeInsertQuery(amrp.getAccountInformation());
        dc.executeUpdateQuery(amrp1.getAccountInformation(), amrp1.getAlterAccountToo());
        dc.executeUpdateQuery(amrp1.getAlterAccountToo(), amrp1.getAccountInformation());
    }

    /**
     * DatabaseConnector constructor without arguments
     */
    public DatabaseConnector() {
        this(defaultConnectionUrl);
    }

    /**
     * DatabaseConnector constructor with arguments
     *
     * @param connectionUrl the url to which to connect to
     */
    public DatabaseConnector(String connectionUrl) {
        getClassByName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        connectionToDatabase = connectToDatabase(connectionUrl);
        statement = createStatement(connectionToDatabase);
    }

    /**
     * Methode to get a class by giving its name as parameter
     *
     * @param nameOfClass the name of the class you want to get
     * @return returns class with the name given or null
     */
    private Class getClassByName(String nameOfClass) {
        Class classToBeReturned = null;
        try {
            classToBeReturned = Class.forName(nameOfClass);
        } catch (ClassNotFoundException ex) {
            System.out.println("-Could not find class with the name: " + nameOfClass);
            Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return classToBeReturned;
    }

    /**
     * Methode to create a connection to a SQLServer
     *
     * @param urlToConnectTo Url to which to connect to
     * @return the connection that is made, if failed returns null
     */
    private Connection connectToDatabase(String urlToConnectTo) throws NullPointerException{
        try {
            System.out.println(defaultConnectionUrl);
            System.out.println("-Trying to connect to database server on"
                    + urlToConnectTo);
            connectionToDatabase = DriverManager.getConnection(urlToConnectTo);
            
            System.out.println("-Succesfully connected");
        } catch (SQLException ex) {
            if (ex.toString().contains("The TCP/IP connection to the host")) {
               Log.print("Connecting to database URL failed", Log.SEVERE);
               return null;
            }
            Log.print("Cannot create connection with the database. Is the database up and running?", Log.SEVERE);
			Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
		}
        return connectionToDatabase;
    }

    /**
     * Methode to create statement
     *
     * @param connectionToDatabase The connection for which to create a
     * statement
     * @return The newly created statement or null if failed
     */
    private Statement createStatement(Connection connectionToDatabase) {
        try {
            System.out.println("-Trying to create statement");
            this.statement = connectionToDatabase.createStatement();
            System.out.println("-Succesfully created statement");
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
			System.out.println("CANNOT CREATE STATEMENT ");
        }
        return this.statement;
    }

    public int executeUpdateQuery(String updateQuery) {
        System.out.println("update query used: " + updateQuery);

        return executeUpdate(updateQuery);
    }

    public int executeUpdateQuery(HashMap<String, String> originel,
            HashMap<String, String> alterTo) {
        int returnInt;
        String where = "";

        Iterator<Map.Entry<String, String>> orgiAcc = originel.entrySet().iterator();
        while (orgiAcc.hasNext()) {
            Map.Entry<String, String> entry = orgiAcc.next();
            if (entry.getValue() == null || entry.getValue().isEmpty()) {
                orgiAcc.remove();
            }
        }
        Iterator<Map.Entry<String, String>> iterator = alterTo.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = iterator.next();
            if (entry.getValue() == null || entry.getValue().isEmpty()) {
                iterator.remove();
            }
        }

        try {
            if (originel.containsKey("windowsLogingID")) {
                where += "where [Windows Login ID] = '" + originel.get("windowsLogingID") + "'";
            } else {
                
                System.out.println("else of execute update");
                //return -1;
            }
            String set = "set ";
            
            if (alterTo.containsKey("windowsLogingID")) {
                set += "[Windows Login ID]= '" + alterTo.get("windowsLogingID") + "' ";
            }
            if (alterTo.containsKey("username")) {
                if (set.length() > 4) {
                    set +=", ";
                }
                set += "Name= '" + alterTo.get("username") + "' ";
            }
            if (alterTo.containsKey("password")) {
                if (set.length() > 4) {
                    set +=", ";
                }
                set += "Password= '" + encrypt(alterTo.get("password")) + "' ";
            }
            if (alterTo.containsKey("username")) {
                if (set.length() > 4) {
                    set +=", ";
                }
                set += "Usertype= '" + alterTo.get("usertype") + "' ";
            }
            

            String updateQuery =
                    "update [CRONUS Nederland BV$EP User Login]"
                    + set
                    + where;

            returnInt = executeUpdateQuery(updateQuery);
        } catch (NullPointerException ex) {
            System.out.println(where);
            return -1;
        }

        return returnInt;
    }

    public int executeDeleteQuery(HashMap<String, String> map) {
        int returnInt;

        String where = "";
        if (map.containsKey("username")) {
            where += "where Name= '" + map.get("username") + "' ";
        }
        if (map.containsKey("windowsLogingID")) {
            if (where.length() > 0) {
                where += "AND ";
            } else {
                where += "where ";
            }
            where += "[Windows Login ID] = '" + map.get("windowsLogingID") + "' ";
        } else {
            return -1;
        }
        if (map.containsKey("password")) {
            if (where.length() > 0) {
                where += "AND ";
            } else {
                where += "where ";
            }
            where += "Password = '" + map.get("password") + "' ";
        }
        if (map.containsKey("usertype")) {
            if (where.length() > 0) {
                where += "AND ";
            } else {
                where += "where ";
            }
            where += "Usertype = '" + map.get("usertype") + "' ";
        }
        String deleteQuery =
                "delete from [CRONUS Nederland BV$EP User Login] "
                + where;

        returnInt = executeDeleteQuery(deleteQuery);
System.out.println("Delete query used: " + deleteQuery);
        return returnInt;
    }

    public int executeDeleteQuery(String deleteQuery) {
        System.out.println("Delete query used: " + deleteQuery);

        return executeUpdate(deleteQuery);
    }

    public int executeInsertQuery(HashMap<String, String> map) {
        int returnInt;

        String insertQuery =
                "insert into [CRONUS Nederland BV$EP User Login] "
                + "(Name ,[Windows Login ID], Password, Usertype)"
                + "Values('" + map.get("username") + "', "
                + "'" + map.get("windowsLogingID") + "',"
                + "'" + map.get("password") + "',"
                + "" + map.get("usertype") + ""
                + ") ";

        returnInt = executeInsertQuery(insertQuery);

        return returnInt;
    }

    public int executeInsertQuery(String insertQuery) {
        System.out.println("insert query used: " + insertQuery);

        return executeUpdate(insertQuery);
    }

    private int executeUpdate(String query) {
        int returnInt = 0;
        try {
            returnInt = this.statement.executeUpdate(query);
            statement.clearBatch();
        } catch (SQLException ex) {
            if (ex.toString().contains("Violation of PRIMARY KEY")) {
                System.out.println("Value is already in the database");
                return 0;
            }
            Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return returnInt;
    }

    /**
     * Methode to execute queriess on a SQLServer
     *
     * @param query The query that needs to be executed
     * @return Returns the result of the query that executed
     */
    public ResultSet executeQuery(String query) {
        System.out.println("-Trying to execute query " + query);
        ResultSet rs = null;

        try {
            rs = statement.executeQuery(query);
            statement.clearBatch();
            this.result = true;
        } catch (SQLServerException ex) {
            Log.print("The query you want to execute is wrong.", Log.SEVERE);
            this.result = false;
            //Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLTimeoutException ex) {
            Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
        }

        // this.result = true;

        return rs;
    }

    public ArrayList<String[]> executeQuery2(String query) {
        ResultSet rs = executeQuery(query);
        ArrayList<String[]> list = null;
        if (this.getQueryResult()) {
            list = convertResultSetToCollection(rs);
            System.out.println(query);
            for (String[] row : list) {
                for (int i = 0; i < row.length; i++) {
                    //           System.out.print(row[i] + "\t");
                }
                //       System.out.println("--");
            }

            this.result = true;
        }
        return list;

    }
	
	/**
	 * Gets the column names from the result of given query
	 * @param query Query
	 * @return Column names
	 */
	public Object[] getColumnNames(String query) {
		ResultSet rs = executeQuery(query);
		ResultSetMetaData rsmd = null;
		try {
			rsmd = rs.getMetaData();
		} catch (SQLException ex) {
			Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
		}
		int columnCount = 0;
		try {
			columnCount = rsmd.getColumnCount();
		} catch (SQLException ex) {
			Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
		}
		Object[] columnNames = null;
		columnNames = new Object[columnCount];
		for (int i=1; i<=columnCount; i++) {
			try {
				columnNames[i-1] = (Object) rsmd.getColumnName(i);
			} catch (SQLException ex) {
				Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
			}
		}
        this.result = true;
		return columnNames;
    }

    public int getNumberOfCollumns(ResultSet resultSet) {
        int numberOfColumns = 0;
        ResultSetMetaData rsmd;
        if (this.getQueryResult()) {
            try {
                rsmd = resultSet.getMetaData();
                numberOfColumns = rsmd.getColumnCount();
            } catch (SQLException ex) {
                System.out.println("There was a SQL error when we tried getting number of rows");
            }
        }

        return numberOfColumns;
    }

    private ArrayList<String[]> convertResultSetToCollection(ResultSet resultSet) {
        ArrayList<String[]> myList = new ArrayList();
        try {
            int num = getNumberOfCollumns(resultSet);
            while (resultSet.next()) {         // next row
                String[] row = new String[num];
                for (int i = 0; i < num; i++) {    // next collumn
                    row[i] = resultSet.getString(i + 1);
                }
                myList.add(row);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return myList;
    }

    /**
     * Testquery that has a hardcoded query
     */
    public void executeQuery() {
        String query = "select * from [CRONUS Nederland BV$Customer]";
        executeQuery(query);
    }

    public boolean getQueryResult() {
        return result;
    }

    public void closeDatabaseConnector() {
        try {
            if (!statement.isClosed()) {
                statement.close();
            }
            if (!connectionToDatabase.isClosed()) {
                connectionToDatabase.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
         /**
     * Methode for encrypting strings/passwords
     *
     * @param password The string to be encrypted
     * @return Encrypted version of the input
     */
    private static String encrypt(String password) {
        try {
            java.security.MessageDigest md = java.security.MessageDigest.getInstance("MD5");
            byte[] array = md.digest(password.getBytes());
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < array.length; ++i) {
                sb.append(Integer.toHexString((array[i] & 0xFF) | 0x100).substring(1, 3));
            }
            return sb.toString();
        } catch (java.security.NoSuchAlgorithmException e) {
        }
        return null;
    }
}
